﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using GiftNet.IDAL;
using GiftNet.DBUtility;
using System.Collections;
using System.Collections.Generic;
using NHibernate.Engine;

namespace GiftNet.SQLServerDAL
{
    #region dbo.FactoryOrderItems DAL -(by fangyifeng at 2013/12/23 18:42:00)
	public class FactoryOrderItems:IFactoryOrderItems
	{
		
		/// <summary>
		/// Check exists for PrimaryKey
		/// </summary>
		public bool Exists(Guid MyOrderItemID)	
		{
		    try
            {
                NHibernate.ISession session = GiftNet.DBUtility.DBHelper.SessionFactory().OpenSession();
                GiftNet.Model.FactoryOrderItems model = (GiftNet.Model.FactoryOrderItems)session.Load(typeof(GiftNet.Model.FactoryOrderItems), MyOrderItemID);
                session.Close();

                if (model != null)
                    return true;
                else
                    return false;
            }
            catch
            {
                return false;
            }
		}
		
		/// <summary>
		/// Add record for model
		/// </summary>
		public bool Add(GiftNet.Model.FactoryOrderItems FactoryOrderItemsModel)
		{
			bool ret = true;
            NHibernate.ISession session = GiftNet.DBUtility.DBHelper.SessionFactory().OpenSession();
            NHibernate.ITransaction transaction = session.BeginTransaction();

            try
            {
                session.Save(FactoryOrderItemsModel);
                // commit all of the changes to the DB and close the ISession
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                ret = false;
            }
            finally
            {
                session.Close();
            }

            return ret;
		}
		
		/// <summary>
		/// Add record for model and return the key object 
		/// </summary>
		public object AddWithReturnKey(GiftNet.Model.FactoryOrderItems FactoryOrderItemsModel)
		{
		    NHibernate.ISession session = GiftNet.DBUtility.DBHelper.SessionFactory().OpenSession();
            NHibernate.ITransaction transaction = session.BeginTransaction();

            object obj = null;
            try
            {
                obj = session.Save(FactoryOrderItemsModel);
                // commit all of the changes to the DB and close the ISession
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                obj = null;
            }
            finally
            {
                session.Close();
            }

            return obj;
		}
		
		/// <summary>
		/// Update record for model		
		/// </summary>
		public bool Update(GiftNet.Model.FactoryOrderItems FactoryOrderItemsmodel)
		{
			bool ret = true;
            NHibernate.ISession session =  GiftNet.DBUtility.DBHelper.SessionFactory().OpenSession();
            NHibernate.ITransaction transaction = session.BeginTransaction();
            try
            {
                session.Update(FactoryOrderItemsmodel);

                // commit all of the changes to the DB and close the ISession
                transaction.Commit();
                session.Close();
            }
            catch
            {
                transaction.Rollback();
                ret = false;
            }
            finally
            {
                transaction.Dispose();
                session.Close();
            }

            return ret;
		}
		
		
		/// <summary>
		/// Delete record
		/// </summary>
		public bool Delete(GiftNet.Model.FactoryOrderItems FactoryOrderItemsModel)
		{
		    try
            {
                NHibernate.ISession session = GiftNet.DBUtility.DBHelper.SessionFactory().OpenSession();
                NHibernate.ITransaction transaction = session.BeginTransaction();

                session.Delete(FactoryOrderItemsModel);

                // commit all of the changes to the DB and close the ISession
                transaction.Commit();
                session.Close();

                return true;
            }
            catch
            {
                return false;
            }
		}
		
		
		
		/// <summary>
		/// Delete record for PrimaryKey
		/// </summary>
		public bool Delete(Guid MyOrderItemID)
		{
			 bool ret = true;
            NHibernate.ISession session = GiftNet.DBUtility.DBHelper.SessionFactory().OpenSession();
            NHibernate.ITransaction transaction = session.BeginTransaction();

            try
            {
                // Tell NHibernate that this object should be saved
                GiftNet.Model.FactoryOrderItems model = (GiftNet.Model.FactoryOrderItems)session.Load(typeof(GiftNet.Model.FactoryOrderItems), MyOrderItemID);
                // 
                session.Delete(model);
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                transaction.Dispose();
                session.Close();

                ret = false;
            }

            return ret;
		}
		
		
		/// <summary>
		/// Get Model fro PrimaryKey
		/// </summary>
        public GiftNet.Model.FactoryOrderItems GetModel(Guid MyOrderItemID)
		{
			NHibernate.ISession session = GiftNet.DBUtility.DBHelper.SessionFactory().OpenSession();
            NHibernate.ITransaction transaction = session.BeginTransaction();

            GiftNet.Model.FactoryOrderItems model = (GiftNet.Model.FactoryOrderItems)session.Load(typeof(GiftNet.Model.FactoryOrderItems), MyOrderItemID);

            transaction.Commit();
            session.Close();

            return model;
		}
	
		
		/// <summary>
        /// Get ModelList fro Where no suppose Page
        /// </summary>
		/// <param name="iNum">return the record's num,IF not need please insert ""</param>
        /// <param name="strWhere">strWhere,need insert KeyWord->WHERE IF you need this Param</param>
		/// <param name="strOrderBy">strOrderBy,need insert KeyWord->Order By IF you need this Param</param>
        /// <returns>IList&lt;GiftNet.Model.FactoryOrderItems&gt;</returns>
		public IList<GiftNet.Model.FactoryOrderItems> GetModelList(string strWhere)
		{
			NHibernate.ISession session = GiftNet.DBUtility.DBHelper.SessionFactory().OpenSession();
            return session.CreateQuery(strWhere).List<GiftNet.Model.FactoryOrderItems>();
		}
		
		
		/// <summary>
        /// Get ModelList fro Where suppose Page
        /// </summary>
        /// <param name="PageSize">PageSize</param>
        /// <param name="PageIndex">PageIndex</param>
        /// <param name="recordCount">recordCount</param>
        /// <param name="strWhere">strWhere</param>
        /// <returns>IList&lt;GiftNet.Model.FactoryOrderItems&gt;</returns>
		public IList<GiftNet.Model.FactoryOrderItems> GetModelList(int PageSize, int PageIndex, out int recordCount, string strWhere)
		{
			
			recordCount = 0;
            IList sList = GiftNet.DBUtility.DBHelper.Query(" select factoryorderitems from FactoryOrderItems factoryorderitems" +((strWhere.Trim().Length<=0)?"":" where "+strWhere), PageIndex, PageSize);
           
            if (sList.Count <= 0)
                return null;
            IList<GiftNet.Model.FactoryOrderItems> sRetList = new List<GiftNet.Model.FactoryOrderItems>();
            foreach (object obj in sList)
            {
                sRetList.Add((GiftNet.Model.FactoryOrderItems)obj);
            }
            recordCount = GiftNet.DBUtility.DBHelper.Query("select count(1) from FactoryOrderItems factoryorderitems" + ((strWhere.Trim().Length <= 0) ? "" : " where " + strWhere)).Count;
            
            return sRetList;
        }


        #region 自动处理库存满足的工厂订单明细

        public string GetFacktoryPriceId(string BarCode)
        {
            string query = "select PriceID from FactoryPrice aa INNER JOIN ItemBarCode bb ON aa.BarCodeID=bb.BarCodeID WHERE bb.BarCode='" + BarCode + "' and aa.FactoryID='00000000-0000-0000-0000-000000000000'";
            return SqlServerHelper.GetSingle(query).ToString();
        }

        public string GetFacktoryPriceId(string BarCode, string strFactoryID)
        {
            string query = "select PriceID from FactoryPrice aa INNER JOIN ItemBarCode bb ON aa.BarCodeID=bb.BarCodeID WHERE bb.BarCode='" + BarCode + "' and aa.FactoryID='" + strFactoryID + "'";
            object obj = SqlServerHelper.GetSingle(query);
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return "";
            }
        }


        public decimal GetFacktoryPrice(string BarCode, string strFactoryID)
        {
            string query = "select Price from FactoryPrice aa INNER JOIN ItemBarCode bb ON aa.BarCodeID=bb.BarCodeID WHERE bb.BarCode='" + BarCode + "' and aa.FactoryID='" + strFactoryID + "'";
            object obj = SqlServerHelper.GetSingle(query);
            if (obj != null)
            {
                return Convert.ToDecimal(obj.ToString());
            }
            else
            {
                return -1;
            }
        }

        public string GetBarCodeID(string BarCode)
        {
            try
            {
                string query = "SELECT BarCodeID FROM ItemBarCode WHERE BarCode='" + BarCode + "'";
                return SqlServerHelper.GetSingle(query).ToString();
            }
            catch
            {
                return "";
            }
        }


        public decimal GetLowerAlarmQty(string BarCode)
        {
            decimal LowerAlarmQty = 0;
            string query = "SELECT LowerAlarmQty FROM ItemBarCode WHERE BarCode='" + BarCode + "' OR BarCodeID='" + BarCode + "'";
            object obj = SqlServerHelper.GetSingle(query);
            if (obj == null) { obj = 0; }
            decimal.TryParse(obj.ToString(), out LowerAlarmQty);
            return LowerAlarmQty;
        }

        /// <summary>
        /// 获取实际库存
        /// </summary>
        /// <param name="BarCodeID"></param>
        /// <returns></returns>
        public decimal GetItemStock(string BarCodeID)
        {
            decimal dStockQty = 0;
            string query = "SELECT StockQty FROM ItemBarCode WHERE BarCodeID='" + BarCodeID + "'";
            object obj = SqlServerHelper.GetSingle(query);
            if (obj == null) { obj = 0; }
            decimal.TryParse(obj.ToString(), out dStockQty);
            return dStockQty;
        }

        /// <summary>
        /// 获取上午订货量
        /// </summary>
        /// <param name="BarCodeID"></param>
        /// <returns></returns>
        public decimal GetMorningFactoryQuantity(string BarCodeID)
        {
            decimal dQty = 0;
            string query = "SELECT SUM(aa.Quantity) FROM dbo.FactoryOrderItems aa INNER JOIN dbo.FactoryPrice bb ON aa.PriceID=bb.PriceID INNER JOIN dbo.ItemBarCode cc ON bb.BarCodeID=cc.BarCodeID WHERE aa.IsDel=0 and aa.IsMyOrder=0 and aa.Quantity<>0 and cc.BarCodeID='" + BarCodeID + "' AND aa.OrderItemStatus=1 and bb.FactoryID<>'00000000-0000-0000-0000-000000000000' GROUP BY cc.BarCodeID";
            object obj = SqlServerHelper.GetSingle(query);
            if (obj == null) { obj = 0; }
            decimal.TryParse(obj.ToString(), out dQty);
            return dQty;
        }

        /// <summary>
        /// 获取上午客户订单要的量
        /// </summary>
        /// <param name="BarCodeID"></param>
        /// <returns></returns>
        public decimal GetMorningCustomerQuantity(string BarCodeID)
        {
            decimal dStockQty = 0;
            //是否分离出手动订货的
            string query = "SELECT SUM(Quantity) FROM ProOrderPublic aa WHERE aa.BarCodeGUID='" + BarCodeID + "' AND aa.SiteCode<>'N' AND ProOrdGuid IN (SELECT ProOrdGuid FROM FactoryOrderItems aa INNER JOIN dbo.FactoryPrice bb ON aa.PriceID=bb.PriceID WHERE OrderItemStatus=1 and aa.IsMyOrder=0 and bb.FactoryID<>'00000000-0000-0000-0000-000000000000')";
            object obj = SqlServerHelper.GetSingle(query);
            if (obj == null) { obj = 0; }
            decimal.TryParse(obj.ToString(), out dStockQty);
            return dStockQty;
        }

        /// <summary>
        /// 本次客户订的量(现在加的量)
        /// </summary>
        /// <param name="BarCodeID"></param>
        /// <returns></returns>
        public decimal GetMorningCustomerQuantityNow(string BarCodeID, string strFactoryID)
        {
            decimal dThis = 0;

            //没包含手工备货的
            //string query = "SELECT SUM(Quantity) FROM ProOrderPublic aa WHERE aa.BarCodeGUID='" + BarCodeID + "' AND ProOrdGuid IN (SELECT ProOrdGuid FROM FactoryOrderItems aa INNER JOIN dbo.FactoryPrice bb ON aa.PriceID=bb.PriceID WHERE OrderItemStatus=0 and aa.IsMyOrder=0 and bb.FactoryID<>'00000000-0000-0000-0000-000000000000')";
            //string query = "SELECT SUM(Quantity) FROM ProOrderPublic aa WHERE aa.BarCodeGUID='" + BarCodeID + "' AND aa.SiteCode<>'N' AND ProOrdGuid IN (SELECT ProOrdGuid FROM FactoryOrderItems aa INNER JOIN dbo.FactoryPrice bb ON aa.PriceID=bb.PriceID WHERE OrderItemStatus=0 and aa.IsMyOrder=0 and bb.FactoryID<>'00000000-0000-0000-0000-000000000000')";
            //string query = "SELECT SUM(Quantity) FROM ProOrderPublic aa WHERE aa.BarCodeGUID='" + BarCodeID + "' AND aa.SiteCode<>'N' AND ProOrdGuid IN (SELECT ProOrdGuid FROM FactoryOrderItems aa INNER JOIN dbo.FactoryPrice bb ON aa.PriceID=bb.PriceID WHERE OrderItemStatus=0 and aa.IsMyOrder=0 and bb.FactoryID<>'00000000-0000-0000-0000-000000000000' and bb.FactoryID='" + strFactoryID + "')";
            string query = "SELECT SUM(Quantity) FROM ProOrderPublic aa WHERE aa.BarCodeGUID='" + BarCodeID + "' AND aa.SiteCode<>'N' AND ProOrdGuid IN (SELECT ProOrdGuid FROM FactoryOrderItems aa INNER JOIN dbo.FactoryPrice bb ON aa.PriceID=bb.PriceID WHERE OrderItemStatus=0 and aa.IsMyOrder=0 and bb.FactoryID='" + strFactoryID + "')";
            object obj = SqlServerHelper.GetSingle(query);
            if (obj == null) { obj = 0; }
            decimal.TryParse(obj.ToString(), out dThis);

            return dThis;
        }

        /// <summary>
        /// 获取动态库存
        /// </summary>
        /// <param name="BarCodeID"></param>
        /// <returns></returns>
        public decimal GetDynamicStockCount(string BarCodeID)
        {
            decimal dDynamicStockQty = 0;
            //实际库存+上午已订货数量
            dDynamicStockQty = this.GetItemStock(BarCodeID) + this.GetMorningFactoryQuantity(BarCodeID);
            return dDynamicStockQty;
        }

        /// <summary>
        /// 获取订货量
        /// </summary>
        /// <param name="BarCodeID"></param>
        /// <returns></returns>
        public decimal GetNeedCount(string BarCodeID, decimal dFactoryOrderQuantity)
        {
            decimal dWantOrderQuantity = 0;
            //当前库存+上午订的量-上午客户要的量-现在加单的量-预警
            dWantOrderQuantity = this.GetItemStock(BarCodeID) + this.GetMorningFactoryQuantity(BarCodeID) - this.GetMorningCustomerQuantity(BarCodeID) - dFactoryOrderQuantity - GetLowerAlarmQty(BarCodeID);
            /*
            if (dWantOrderQuantity > 0)
            {
                dWantOrderQuantity = 0;
            }
            else
            {
                dWantOrderQuantity = System.Math.Abs(dWantOrderQuantity);
            }
             */
            return dWantOrderQuantity;
        }

        /// <summary>
        /// 检测是否有货
        /// </summary>
        /// <param name="BarCodeID"></param>
        /// <param name="dFactoryOrderQuantity"></param>
        /// <param name="dStockQty"></param>
        /// <returns></returns>
        public bool CheckStock(string BarCodeID, decimal dFactoryOrderQuantity)
        {
            decimal dd = GetNeedCount(BarCodeID, dFactoryOrderQuantity);
            dd = NeedCount(dd);
            if (dd == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public decimal NeedCount(decimal dd)
        {
            if (dd > 0)
            {
                dd = 0;
            }
            else
            {
                dd = System.Math.Abs(dd);
            }
            return dd;
        }
        public bool CheckStock2(string BarCodeID, decimal dFactoryOrderQuantity)
        {
            //如果只上午处理一次的话就只取实际的库存
            //decimal dStockQty = this.GetItemStock(BarCodeID) ;
            decimal dStockQty = this.GetItemStock(BarCodeID) + this.GetMorningFactoryQuantity(BarCodeID) - GetMorningCustomerQuantity(BarCodeID);
            if (dStockQty > dFactoryOrderQuantity)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int MarkedForDeletionByBarCodeID(string BarCodeID)
        {
            string query = "UPDATE FactoryOrderItems SET IsDel=1 WHERE MyOrderGuid='" + Guid.Empty.ToString() + "' and IsMyOrder=0 and OrderItemStatus=0 and StockIn<=0 and PriceID IN (SELECT PriceID FROM FactoryPrice WHERE BarCodeID='" + BarCodeID + "')";
            return SqlServerHelper.ExecuteSql(query);
        }

        public int MarkedForDeletionByFactoryOrderItemsID(string FactoryOrderItemsID)
        {
            string query = "UPDATE FactoryOrderItems SET IsDel=1 WHERE MyOrderItemID='" + FactoryOrderItemsID + "'";
            return SqlServerHelper.ExecuteSql(query);
        }

        public DataTable AutoCheckList()
        {
            //一个货号同天出现在几个工厂的话就让配货员手动确定数量(HAVING COUNT(*)=1)
            string query = "SELECT cc.BarCodeID, SUM(aa.Quantity) as Quantity,SUM(aa.ManualQuantity) as ManualQuantity  FROM dbo.FactoryOrderItems aa INNER JOIN dbo.FactoryPrice bb ON aa.PriceID=bb.PriceID INNER JOIN dbo.ItemBarCode cc ON bb.BarCodeID=cc.BarCodeID WHERE aa.IsDel=0 and aa.IsMyOrder=0 and aa.Quantity<>0 and OrderItemStatus=0 and bb.FactoryID<>'00000000-0000-0000-0000-000000000000' GROUP BY cc.BarCodeID HAVING COUNT(*)=1";
            return SqlServerHelper.Query(query).Tables[0];
        }


        public DataTable AutoCheckStockCurrentFactory(string strFactoryID)
        {
            //一个货号同天出现在几个工厂的话就让配货员手动确定数量(HAVING COUNT(*)=1)
            string query = "SELECT cc.BarCodeID, SUM(aa.Quantity) as Quantity,SUM(aa.ManualQuantity) as ManualQuantity  FROM dbo.FactoryOrderItems aa INNER JOIN dbo.FactoryPrice bb ON aa.PriceID=bb.PriceID INNER JOIN dbo.ItemBarCode cc ON bb.BarCodeID=cc.BarCodeID WHERE bb.FactoryID='" + strFactoryID + "' AND aa.IsDel=0 and aa.IsMyOrder=0 and aa.Quantity<>0 and OrderItemStatus=0 and bb.FactoryID<>'00000000-0000-0000-0000-000000000000' GROUP BY cc.BarCodeID HAVING COUNT(*)=1";
            return SqlServerHelper.Query(query).Tables[0];
        }

        public DataTable FactoryOrderExcelList(string strFactoryOrderGUID)
        {
            string strProID = "";
            string strPhotoUrl = "";
            string strProCode = "";
            //string strSizeAndQty = "";
            StringBuilder strbuilderSizeAndQty = new StringBuilder();
            //string strSizeAndQty2 = "";
            StringBuilder strbuilderSizeAndQty2 = new StringBuilder();
            decimal dRealQty = 0;
            decimal dPrice = 0;
            decimal dAmount = 0;
            string query = "SELECT cc.proID,pp.code,ee.FileUrl+'Mini/'+ee.name as photourl,cc.BarCode,cc.Size, aa.* FROM FactoryOrderItems aa inner join dbo.FactoryPrice bb on aa.priceid=bb.priceid INNER JOIN ItemBarCode cc ON bb.BarCodeID=cc.BarCodeID INNER JOIN product pp ON cc.proID=pp.proID INNER JOIN ProductPhoto dd ON cc.ProID=dd.Product INNER JOIN FileInfo ee ON dd.fileinfo=ee.fileid where aa.MyOrderGuid='" + strFactoryOrderGUID + "' and aa.IsDel=0 and aa.IsMyOrder=0 and aa.OrderItemStatus<3 and aa.Quantity<>0 and dd.IsMainImage=1 order by cc.proID";
            //处理DataTable,
            DataTable dtItems = SqlServerHelper.Query(query).Tables[0];
            DataTable dtLast = dtItems.Copy();
            dtLast.Rows.Clear();
            for (int i = 0; i < dtItems.Rows.Count; i++)
            {
                DataRow row = dtItems.Rows[i];
                strProID = row["proID"].ToString();
                strPhotoUrl = row["photourl"].ToString();
                strProCode = row["code"].ToString();
                //strSizeAndQty = strSizeAndQty + Convert.ToDecimal(row["Quantity"]).ToString("F0") + "件" + row["BarCode"].ToString() + "," + Environment.NewLine;
                if (strbuilderSizeAndQty.Length > 0)
                    strbuilderSizeAndQty.Append(",");
                strbuilderSizeAndQty.Append(Convert.ToDecimal(row["Quantity"]).ToString("F0") + "件" + row["BarCode"].ToString());

                if (strbuilderSizeAndQty2.Length > 0)
                    strbuilderSizeAndQty2.Append(",");
                strbuilderSizeAndQty2.Append(Convert.ToDecimal(row["Quantity"]).ToString("F0") + "件" + row["Size"].ToString()).Replace("one-size", "");

                //strSizeAndQty2 = strSizeAndQty2 + Convert.ToDecimal(row["Quantity"]).ToString("F0") + "件" + row["Size"].ToString() + "," + Environment.NewLine;
                //dRealQty = dRealQty+row["Quantity"].ToString();
                //dPrice = row["proID"].ToString();
                //dAmount = row["proID"].ToString();
                //产品ID变换或最后一行结束时的处理
                if (i == dtItems.Rows.Count - 1 || (i < dtItems.Rows.Count - 1 && dtItems.Rows[i]["proID"].ToString() != dtItems.Rows[i + 1]["proID"].ToString()))
                {
                    DataRow rr = dtLast.NewRow();
                    rr["proID"] = strProID;
                    rr["photourl"] = strPhotoUrl;
                    rr["code"] = strProCode;//因为这里并不是这个款号，而是留给工厂自已填
                    //if (strSizeAndQty.EndsWith(","))
                    //{
                    //    strSizeAndQty = strSizeAndQty.Substring(0, strSizeAndQty.Length-1);
                    //}
                    //if (strSizeAndQty2.EndsWith(","))
                    //{
                    //    strSizeAndQty2 = strSizeAndQty2.Substring(0, strSizeAndQty2.Length - 1);
                    //}
                    rr["remark"] = strbuilderSizeAndQty.ToString();
                    rr["StockInDesc"] = strbuilderSizeAndQty2.ToString();
                    dtLast.Rows.Add(rr);
                    //重置
                    //strSizeAndQty = "";
                    //strSizeAndQty2 = "";
                    strbuilderSizeAndQty = new StringBuilder();
                    strbuilderSizeAndQty2 = new StringBuilder();
                    dRealQty = 0;
                    dPrice = 0;
                    dAmount = 0;
                }
            }
            return dtLast;
        }

        #endregion
    }


	#endregion
}

